Redshift：最適化が必要なテーブルを探す拡張viewをつかってみた

みかみ

2016.12.15

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは。三上です。

パンジーに裏表があることを、花屋さんに教わりました。（そういえば、みんな同じ方向向いてる＠＠

やりたいこと

Redshiftのテーブル設計を学びたい！（チューニングもできるようになりたい
AWS Solutions Architect ブログ　Amazon Redshift テーブル設計詳細ガイド：Part 1 序文、事前準備、優先順位付けで紹介されてた、最適化が必要なテーブルを探す拡張view（v_extended_table_info）の中身を見てみたい！

やってみよう

viewをつくる

ブログから、viewのDDLをいただいてきました。

ちら見してみます。

CREATE OR REPLACE VIEW admin.v_extended_table_info AS
WITH tbl_ids AS
(
  SELECT DISTINCT oid
  FROM pg_class c
  WHERE relowner &amp;amp;amp;amp;amp;amp;gt; 1
  AND   relkind = 'r'
),
scan_alerts AS
(
 （中略）
FROM svv_table_info ti 
LEFT JOIN colenc ON colenc.attrelid = ti.table_id 
LEFT JOIN stp ON stp.id = ti.table_id 
LEFT JOIN tbl_scans ON tbl_scans.tbl = ti.table_id 
LEFT JOIN rr_scans ON rr_scans.tbl = ti.table_id
LEFT JOIN pcon ON pcon.conrelid = ti.table_id 
LEFT JOIN scan_alerts ON scan_alerts.table = ti.table_id 
CROSS JOIN cluster_info 
WHERE ti.SCHEMA NOT IN ('pg_internal') 
ORDER BY ti.pct_used DESC;

→うーん（よく分かんない。。

とりま、DDL流してみます。。

まずは、adminスキーマを作って、

defaultdb=# CREATE SCHEMA admin;
CREATE SCHEMA
defaultdb=# ¥dn
List of schemas
  name  | owner
--------+-------
 admin  | root
 public | rdsdb
(2 rows)

いただいてきたDDLをコピペ。

→拡張viewができました。

defaultdb=# ¥d admin.*
               View "admin.v_extended_table_info"
        Column         |            Type             | Modifiers
-----------------------+-----------------------------+-----------
 database              | text                        |
 table_id              | oid                         |
 tablename             | text                        |
 columns               | text                        |
 pk                    | text                        |
 fk                    | text                        |
 max_varchar           | integer                     |
 diststyle             | text                        |
 sortkey               | text                        |
 size                  | text                        |
 tbl_rows              | numeric(38,0)               |
 unsorted              | numeric(5,2)                |
 stats_off             | numeric(5,2)                |
 scans:rr:filt:sel:del | text                        |
 last_scan             | timestamp without time zone |

viewをひもとく

viewの各カラムの説明はコメントに記載していただいてますが、何をどう見れば・・・？（あせ

/**********************************************************************************************
Purpose: Return extended table information for permanent database tables.

Columns:
database: database name
table_id: table oid
tablename: Schema qualified table name
columns: encoded columns / unencoded columns
pk: Y if PK constraint exists, otherwise N
fk: Y if FK constraint exists, otherwise N
max_varchar: Size of the largest column that uses a VARCHAR data type. 
diststyle: diststyle(distkey column): distribution skew ratio
sortkey: sortkey column(sortkey skew)
size: size in MB / minimum table size (percentage storage used)
tbl_rows: Total number of rows in the table
unsorted: Percent of rows in the unsorted space of the table
stats_off: Number that indicates how stale the table's statistics are; 0 is current, 100 is out of date.
scans:rr:filt:sel:del :
  scans: number of scans against the table
  rr: number of range restricted scans (scans which leverage the zone maps) against the table
  filt: number of scans against the table which leveraged filter criteria
  sel: number of scans against the table which triggered an alert for selective query filter
  del: number of scans against the table which triggered an alert for scanning number of deleted rows
last_scan: last time the table was scanned

Notes:
History:
2016-09-12 chriz-bigdata created
**********************************************************************************************/

select句

それぞれのカラム値をどこから取ってきていて、どんな意味があるのか、みてみることにします。

svv_table_info から

データベース名
テーブルID
テーブル名
最大カラムサイズ
分散スタイル
ソートキー
サイズ

を取ってきてます。

（前略）
SELECT ti.database,
       ti.table_id,
       ti.SCHEMA || '.' || ti."table" AS tablename,
       colenc.encoded_cols || '/' || colenc.cols AS "columns",
       pcon.pk,
       pcon.fk,
       ti.max_varchar,
       CASE
         WHEN ti.diststyle NOT IN ('EVEN','ALL') THEN ti.diststyle || ': ' || ti.skew_rows
         ELSE ti.diststyle
       END AS diststyle,
       CASE
         WHEN ti.sortkey1 IS NOT NULL AND ti.sortkey1_enc IS NOT NULL THEN ti.sortkey1 || '(' || nvl (skew_sortkey1,0) || ')'
         WHEN ti.sortkey1 IS NOT NULL THEN ti.sortkey1
         ELSE NULL
       END AS "sortkey",
       ti.size || '/' || CASE
         WHEN stp.sum_r = stp.sum_sr OR stp.sum_sr = 0 THEN
           CASE
             WHEN "diststyle" = 'EVEN' THEN (stp.pop_slices*(colenc.cols + 3))
             WHEN SUBSTRING("diststyle",1,3) = 'KEY' THEN (stp.pop_slices*(colenc.cols + 3))
             WHEN "diststyle" = 'ALL' THEN (cluster_info.node_count*(colenc.cols + 3))
           END 
         ELSE
           CASE
             WHEN "diststyle" = 'EVEN' THEN (stp.pop_slices*(colenc.cols + 3)*2)
             WHEN SUBSTRING("diststyle",1,3) = 'KEY' THEN (stp.pop_slices*(colenc.cols + 3)*2)
             WHEN "diststyle" = 'ALL' THEN (cluster_info.node_count*(colenc.cols + 3)*2)
           END 
         END|| ' (' || ti.pct_used || ')' AS size,
         ti.tbl_rows,
         ti.unsorted,
         ti.stats_off,
         Nvl(tbl_scans.num_scans,0) || ':' || Nvl(rr_scans.rr_scans,0) || ':' || Nvl(rr_scans.filtered_scans,0) || ':' || Nvl(scan_alerts.selective_scans,0) || ':' || Nvl(scan_alerts.delrows_scans,0) AS "scans:rr:filt:sel:del",tbl_scans.last_scan 
FROM svv_table_info ti 
（後略）

他には、

pg_attribute（colenc）からエンコード情報、

（前略）
colenc AS
(
  SELECT attrelid,
         SUM(CASE WHEN a.attencodingtype = 0 THEN 0 ELSE 1 END) AS encoded_cols,
         COUNT(*) AS cols
  FROM pg_attribute a
  WHERE a.attrelid IN (SELECT oid FROM tbl_ids)
  AND   a.attnum &amp;amp;amp;amp;amp;amp;gt; 0
  GROUP BY a.attrelid
),
（中略）
SELECT ti.database,
       ti.table_id,
       ti.SCHEMA || '.' || ti."table" AS tablename,
       colenc.encoded_cols || '/' || colenc.cols AS "columns",
（後略）

pg_constraint （pcon）からテーブルの制約情報、

（前略）
pcon AS
(
  SELECT conrelid,
         CASE
           WHEN SUM(
             CASE
               WHEN contype = 'p' THEN 1
               ELSE 0
             END 
           ) &amp;amp;amp;amp;amp;amp;gt; 0 THEN 'Y'
           ELSE NULL
         END pk,
         CASE
           WHEN SUM(
             CASE
               WHEN contype = 'f' THEN 1
               ELSE 0
             END 
           ) &amp;amp;amp;amp;amp;amp;gt; 0 THEN 'Y'
           ELSE NULL
         END fk
  FROM pg_constraint
  WHERE conrelid &amp;amp;amp;amp;amp;amp;gt; 0
  AND   conrelid IN (SELECT oid FROM tbl_ids)
  GROUP BY conrelid
),
（中略）
SELECT ti.database,
       ti.table_id,
       ti.SCHEMA || '.' || ti."table" AS tablename,
       colenc.encoded_cols || '/' || colenc.cols AS "columns",
       pcon.pk,
       pcon.fk,
（後略）

stl_alert_event_log （scan_alerts）と stl_scan （tbl_scans, rr_scans）から、実行されたクエリの情報を取得しているようです。

（前略）
scan_alerts AS
(
  SELECT s.tbl AS TABLE,
         Nvl(SUM(CASE WHEN TRIM(SPLIT_PART(l.event,':',1)) = 'Very selective query filter' THEN 1 ELSE 0 END),0) AS selective_scans,
         Nvl(SUM(CASE WHEN TRIM(SPLIT_PART(l.event,':',1)) = 'Scanned a large number of deleted rows' THEN 1 ELSE 0 END),0) AS delrows_scans
  FROM stl_alert_event_log AS l
    JOIN stl_scan AS s
      ON s.query = l.query
     AND s.slice = l.slice
     AND s.segment = l.segment
     AND s.step = l.step
  WHERE l.userid &amp;amp;amp;amp;amp;amp;gt; 1
  AND   s.slice = 0
  AND   s.tbl IN (SELECT oid FROM tbl_ids)
  AND   l.event_time &amp;amp;amp;amp;amp;amp;gt;= Dateadd (DAY,-7,CURRENT_DATE)
  AND   TRIM(SPLIT_PART(l.event,':',1)) IN ('Very selective query filter','Scanned a large number of deleted rows')
  GROUP BY 1
),
tbl_scans AS
(
  SELECT tbl,
         MAX(endtime) last_scan,
         Nvl(COUNT(DISTINCT query || LPAD(segment,3,'0')),0) num_scans
  FROM stl_scan s
  WHERE s.userid &amp;amp;amp;amp;amp;amp;gt; 1
  AND   s.tbl IN (SELECT oid FROM tbl_ids)
  GROUP BY tbl
),
rr_scans AS
(
SELECT tbl,
NVL(SUM(CASE WHEN is_rrscan='t' THEN 1 ELSE 0 END),0) rr_scans,
NVL(SUM(CASE WHEN p.info like 'Filter:%' and p.nodeid &amp;amp;amp;amp;amp;amp;gt; 0 THEN 1 ELSE 0 END),0) filtered_scans,
Nvl(COUNT(DISTINCT s.query || LPAD(s.segment,3,'0')),0) num_scans
  FROM stl_scan s
  JOIN stl_plan_info i on (s.userid=i.userid and s.query=i.query and s.segment=i.segment and s.step=i.step)
  JOIN stl_explain p on ( i.userid=p.userid and i.query=p.query and i.nodeid=p.nodeid  )
  WHERE s.userid &amp;amp;amp;amp;amp;amp;gt; 1
  AND s.type = 2
  AND s.slice = 0
  AND   s.tbl IN (SELECT oid FROM tbl_ids)
  GROUP BY tbl
),
（中略）
SELECT ti.database,
（中略）
         Nvl(tbl_scans.num_scans,0) || ':' || Nvl(rr_scans.rr_scans,0) || ':' || Nvl(rr_scans.filtered_scans,0) || ':' || Nvl(scan_alerts.selective_scans,0) || ':' || Nvl(scan_alerts.delrows_scans,0) AS "scans:rr:filt:sel:del",tbl_scans.last_scan 
（後略）

ちなみに、PostgreSQL共通のシステムカタログ（pg_*）、Redshiftでは微妙にテーブル定義違うのですね。。

pg_attributeテーブルのattencodingtypeカラム、PostgreSQLリファレンスには見当たりません。。

でもRedshiftでテーブル定義見てみたら、ちゃんといました。。

defaultdb=# ¥d pg_attribute
    Table "pg_catalog.pg_attribute"
     Column      |   Type   | Modifiers
-----------------+----------+-----------
 attrelid        | oid      | not null
 attname         | name     | not null
 atttypid        | oid      | not null
 attstattarget   | integer  | not null
 attlen          | smallint | not null
 attnum          | smallint | not null
 attndims        | integer  | not null
 attcacheoff     | integer  | not null
 atttypmod       | integer  | not null
 attbyval        | boolean  | not null
 attstorage      | "char"   | not null
 attalign        | "char"   | not null
 attnotnull      | boolean  | not null
 atthasdef       | boolean  | not null
 attisdropped    | boolean  | not null
 attislocal      | boolean  | not null
 attinhcount     | integer  | not null
 attisdistkey    | boolean  | not null
 attispreloaded  | boolean  | not null
 attsortkeyord   | integer  | not null
 attencodingtype | smallint | not null
 attencrypttype  | smallint | not null
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

wehere句

レコード抽出条件を見てみます。

（前略）
FROM svv_table_info ti 
LEFT JOIN colenc ON colenc.attrelid = ti.table_id 
LEFT JOIN stp ON stp.id = ti.table_id 
LEFT JOIN tbl_scans ON tbl_scans.tbl = ti.table_id 
LEFT JOIN rr_scans ON rr_scans.tbl = ti.table_id
LEFT JOIN pcon ON pcon.conrelid = ti.table_id 
LEFT JOIN scan_alerts ON scan_alerts.table = ti.table_id 
CROSS JOIN cluster_info 
WHERE ti.SCHEMA NOT IN ('pg_internal') 
ORDER BY ti.pct_used DESC;

→ユーザー定義テーブルを対象としている模様。。

viewにデータをいれる

作成したviewの中身を見てみます。

defaultdb=# select count(*) from admin.v_extended_table_info;
 count
-------
     0
(1 row)

→何も入ってません。。（まだテーブル作ってないので。。

抽出元の svv_table_info にも、

defaultdb=# select count(*) from svv_table_info;
 count
-------
     0
(1 row)

やはり何もありません。

→サンプルデータを用意します。

サンプルデータ

以下のcsvデータを使います。

全国の郵便番号データcsv ：日本郵便郵便番号データダウンロード

全国の住所データcsv：住所.jp 住所データCSV

テーブル作成＆データロード

以下の手順で、Redshiftにデータを入れました。

１）csvファイル文字コード変換（sjis→utf8）

$ iconv -c -f SJIS -t UTF8 KEN_ALL.CSV &amp;amp;gt; KEN_ALL_utf8.CSV
$ iconv -c -f SJIS -t UTF8 zenkoku.csv &amp;amp;gt; zenkoku_utf8.csv

２）ファイルアップロード（local→S3）

$ aws --profile mikami s3 cp --recursive data s3://cm-mikami-test/

※ AWS CLIのconfigにprofile（mikami）を設定済み

※ コマンド実行時のカレントディレクトリ配下の data フォルダにアップロードするファイル（KEN_ALL_utf8.CSV, zenkoku_utf8.csv）を配置済み

３）データロード（S3→Redshift）

PGPASSWORD=[password] psql -f execute_sql.sql -h [End Point] -U [user] -d [dbname] -p 5439

create table if not exists m_postal_code(
    id varchar(255),
    code_pre varchar(255),
    code varchar(255),
    pref_kana varchar(255),
    city_kana varchar(255),
    addr_kana varchar(255),
    pref varchar(255),
    city varchar(255),
    addr varchar(255),
    col1 varchar(255),
    col2 varchar(255),
    col3 varchar(255),
    col4 varchar(255),
    col5 varchar(255),
    col6 varchar(255)
)
DISTSTYLE EVEN
SORTKEY (code);

create table m_addr(
    addr_cd varchar(255),
    pref_cd varchar(255),
    city_cd varchar(255),
    area_cd varchar(255),
    postal_cd varchar(255),
    office_cd varchar(255),
    delete_flg varchar(255),
    pref varchar(255),
    pref_kana varchar(255),
    city varchar(255),
    city_kana varchar(255),
    addr varchar(255),
    addr_kana varchar(255),
    addr_description varchar(255),
    col1 varchar(255),
    col2 varchar(255),
    col3 varchar(255),
    col4 varchar(255),
    col5 varchar(255),
    col6 varchar(255),
    col7 varchar(255),
    col8 varchar(255)
)
DISTSTYLE EVEN
SORTKEY (postal_cd);

copy m_postal_code from 's3://cm-mikami-test/KEN_ALL_utf8.CSV' CREDENTIALS 'aws_access_key_id=[access_key];aws_secret_access_key=[secret_key]' delimiter ',' REMOVEQUOTES TRUNCATECOLUMNS;
copy m_addr from 's3://cm-mikami-test/zenkoku_utf8.csv' CREDENTIALS 'aws_access_key_id=[access_key];aws_secret_access_key=[secret_key]' delimiter ',' REMOVEQUOTES TRUNCATECOLUMNS;

拡張viewを再確認

先ほど何も入っていなかった拡張view（v_extended_table_info）をもう一度確認してみます。

defaultdb=# select count(*) from admin.v_extended_table_info;
 count
-------
     2
(1 row)


defaultdb=# select * from admin.v_extended_table_info;
 database  | table_id |      tablename       | columns | pk | fk | max_varchar | diststyle |     sortkey     |      size       | tbl_rows | unsorted | stats_off | scans:rr:filt:sel:del |         last_scan
-----------+----------+----------------------+---------+----+----+-------------+-----------+-----------------+-----------------+----------+----------+-----------+-----------------------+----------------------------
 defaultdb |   100189 | public.m_addr        | 22/22   |    |    |         255 | EVEN      | postal_cd(1.00) | 100/50 (0.0262) |   148848 |     0.00 |      0.00 | 26:0:0:0:0            | 2016-12-19 05:53:06.467259
 defaultdb |   100187 | public.m_postal_code | 15/15   |    |    |         255 | EVEN      | code(1.00)      | 72/36 (0.0188)  |   124056 |     0.00 |      0.00 | 19:0:0:0:0            | 2016-12-19 05:53:00.423304
(2 rows)

→テーブル情報が格納されました！

viewのデータを更新

作成したテーブル（m_addr, m_postal_code）に対して、適当に何本かクエリを投げた後、拡張viewを見てみると

defaultdb=# select * from admin.v_extended_table_info;
 database  | table_id |      tablename       | columns | pk | fk | max_varchar | diststyle |     sortkey     |      size       | tbl_rows | unsorted | stats_off | scans:rr:filt:sel:del |         last_scan
-----------+----------+----------------------+---------+----+----+-------------+-----------+-----------------+-----------------+----------+----------+-----------+-----------------------+----------------------------
 defaultdb |   100189 | public.m_addr        | 22/22   |    |    |         255 | EVEN      | postal_cd(1.00) | 100/50 (0.0262) |   148848 |     0.00 |      0.00 | 35:4:5:1:0            | 2016-12-19 08:14:35.072146
 defaultdb |   100187 | public.m_postal_code | 15/15   |    |    |         255 | EVEN      | code(1.00)      | 72/36 (0.0188)  |   124056 |     0.00 |      0.00 | 21:2:2:0:0            | 2016-12-19 08:15:02.444509
(2 rows)

→scans:rr:filt:sel:del と last_scan のカラム値が更新されています。

scans:rr:filt:sel:del カラムが大事っぽい（？！

それぞれの数値の意味を見直してみます。

scans：スキャン総数
rr：範囲検索実行回数
filt：フィルタを使った検索の実行回数（インデックスが効かなかった検索の実行回数？
sel：select時にアラート（Very selective query filter）が発生した検索の実行回数（SORTキー設定を推奨
del：delete時にアラート（Scanned a large number of deleted rows）が発生した検索の実行回数（VACUUMコマンド実行を推奨
- 参考：PostgreSQL 9.6.0文書 14.1. EXPLAINの利用
- 参考：AWS システムテーブルリファレンス STL_ALERT_EVENT_LOG

viewをつかう

最適化の優先順位を決めるには・・・？

Amazon Redshift テーブル設計詳細ガイド：Part 1 序文、事前準備、優先順位付け

最適化をする対象の表の重要性を見積もるには「スキャン頻度」と「テーブルサイズ」が最も関連性が高い指標

だそうです。

拡張viewを使ったサンプルクエリを見てみます。

基本：最適化が必要なテーブルの優先度付け（特に遅い処理があるわけではない場合）

以下のクエリを実行します。

SELECT 
  * 
FROM 
  admin.v_extended_table_info 
WHERE 
  table_id IN (
    SELECT 
      DISTINCT tbl 
    FROM 
      stl_scan 
    WHERE 
      type = 2
  ) 
ORDER BY 
  SPLIT_PART("scans:rr:filt:sel:del", ':', 1):: int DESC, 
  size DESC;

実行結果

 database  | table_id |      tablename       | columns | pk | fk | max_varchar | diststyle |     sortkey     |      size       | tbl_rows | unsorted | stats_off | scans:rr:filt:sel:del |         last_scan
-----------+----------+----------------------+---------+----+----+-------------+-----------+-----------------+-----------------+----------+----------+-----------+-----------------------+----------------------------
 defaultdb |   100189 | public.m_addr        | 22/22   |    |    |         255 | EVEN      | postal_cd(1.00) | 100/50 (0.0262) |   148848 |     0.00 |      0.00 | 35:4:5:1:0            | 2016-12-19 08:14:35.072146
 defaultdb |   100187 | public.m_postal_code | 15/15   |    |    |         255 | EVEN      | code(1.00)      | 72/36 (0.0188)  |   124056 |     0.00 |      0.00 | 21:2:2:0:0            | 2016-12-19 08:15:02.444509
(2 rows)

→ユーザー定義テーブルで、スキャン回数が多く、サイズが大きいテーブルから順に表示されました。

クエリ限定：遅いクエリが分かっている場合

ブログのサンプルクエリでは、クエリIDが指定されています。

クエリIDは実行環境依存なので、ID指定部を STL_SCAN から遅いクエリを取得するサブクエリに置き換えて実行してみました。

SELECT 
  * 
FROM 
  admin.v_extended_table_info 
WHERE 
  table_id IN (
    SELECT 
      DISTINCT tbl 
    FROM 
      stl_scan 
    WHERE 
      type = 2 
      AND query = (
        select 
          distinct query 
        from 
          stl_scan 
        where 
          (
            tbl = 100187 
            or tbl = 100189
          ) 
          and (endtime - starttime &amp;gt; 1000000) 
        order by 
          (endtime - starttime) desc 
        limit 
          1
      )
  ) 
ORDER BY 
  SPLIT_PART("scans:rr:filt:sel:del", ':', 1):: int DESC, 
  size DESC;

実行結果

 database  | table_id |   tablename   | columns | pk | fk | max_varchar | diststyle |     sortkey     |      size       | tbl_rows | unsorted | stats_off | scans:rr:filt:sel:del |         last_scan
-----------+----------+---------------+---------+----+----+-------------+-----------+-----------------+-----------------+----------+----------+-----------+-----------------------+----------------------------
 defaultdb |   100189 | public.m_addr | 22/22   |    |    |         255 | EVEN      | postal_cd(1.00) | 100/50 (0.0262) |   148848 |     0.00 |      0.00 | 35:4:5:1:0            | 2016-12-19 08:14:35.072146
(1 row)

→指定クエリが実行されたテーブルが表示されました。

トランザクション限定：遅いトランザクションが分かっている場合

クエリ限定版と同様、トランザクションIDを STL_QUERY から取得した実行環境のIDと置き換えて実行してみます。

SELECT 
  * 
FROM 
  admin.v_extended_table_info 
WHERE 
  table_id IN (
    SELECT 
      DISTINCT tbl 
    FROM 
      stl_scan 
    WHERE 
      type = 2 
      AND query IN (
        SELECT 
          query 
        FROM 
          stl_query 
        WHERE 
          xid = 4333
      )
  ) 
ORDER BY 
  SPLIT_PART("scans:rr:filt:sel:del", ':', 1):: int DESC, 
  size DESC;

→該当トランザクション実行時のテーブルが表示されました。

※サンプルデータ（テーブル）少なすぎましたが。。（あせ

まとめ（分かったこと）

最適化が必要なテーブルの優先度付けに大切なのは
- スキャン頻度
- テーブルサイズ
v_extended_table_infoのscans:rr:filt:sel:delカラムで、スキャン頻度やSORT_KEYの見直し、VACCUMEの必要性がわかる
スキャン情報は STL_SCAN で確認できる
トランザクション情報は STL_QUERY で確認できる
STL_ALERT_EVENT_LOG に、アラートが発生したクエリが記録される
システムカタログはPostgreSQL標準よりもRedshiftオリジナル（STL_*, STV_*）を参照した方が良さげ